﻿using Dapper;
using SIG.DAL.Dapper.Model;
using System.Collections.Generic;
using System.Data.Common;

namespace SIG.DAL.Dapper.Repositories
{
    public class FileRepository
    {
        private DbConnection _connection;

        public File GetById(int id)
        {
            File result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Get<File>(id);
            }
            return result;
        }
        public int? Create(File model)
        {
            int? result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Insert(model);
            }
            return result;
        }

        public int Update(File model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Update(model);
            }
            return result;
        }
        public int Delete(File model)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete(model);
            }
            return result;
        }

        public int Delete(int id)
        {
            int result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.Delete<File>(id);
            }
            return result;
        }

        public IEnumerable<File> GetActiveItems()
        {
            IEnumerable<File> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<File>(new { Active = true });
            }
            return result;
        }


        public IEnumerable<File> GetItems()
        {
            IEnumerable<File> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                result = _connection.GetList<File>();
            }
            return result;
        }

        /// <summary>
        /// 获取所有文件分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public IEnumerable<File> GetListPagedActiveItems(int pageIndex, int pageSize,  out int totalCount)
        {
            IEnumerable<File> result;
            using (_connection = Utilities.GetOpenConnection())
            {             
                var sql = "where Active=1 ";            

                totalCount = _connection.RecordCount<File>(sql);
                result = _connection.GetListPaged<File>(pageIndex, pageSize,sql, "Importance desc");
            }
            return result;
        }

        /// <summary>
        /// 获取所有文章分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>

        /// <param name="keyword"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public IEnumerable<File> GetListPagedItems(int pageIndex, int pageSize, string keyword, out int totalCount)
        {
                           
            IEnumerable<File> result;
            using (_connection = Utilities.GetOpenConnection())
            {
                keyword = !string.IsNullOrEmpty(keyword) ? keyword.Replace("[", "[[]").Replace("%", "[%]") : keyword;
                string likeKeyword = "%" + keyword + "%";

                var sql = "where 1=1 ";

                if (!string.IsNullOrEmpty(keyword))
                {
                    sql = sql + " and Title like @Keyword ";
                }
          

                totalCount = _connection.RecordCount<File>(sql,
                    new {  Keyword = likeKeyword });
                result = _connection.GetListPaged<File>(pageIndex, pageSize,
                    sql, "Importance desc",
                    new {  Keyword = likeKeyword});
            }
            return result;
        }
        
    }

}
